package org.common.utils.exceltosql;
import org.apache.commons.io.FileUtils;
import org.common.utils.database.access.DataAccess;
import org.common.utils.database.access.DataSourceTool;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ForkJoinPool;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Stream;

/**
 * 将指定目录下的sql文件批量导入到mysql中
 * @author zhouzhibing
 * @date 2024/12/4
 */
public class SqlFileToMysql {

    private static final Logger log = LoggerFactory.getLogger(SqlFileToMysql.class);

    /**
     * 将指定目录下的sql文件批量导入到mysql中
     * @param host mysql地址
     * @param username 用户名
     * @param password 密码
     * @param directory sql文件目录
     */
    public static void executeToMysql(String host , String username , String password , String directory) {
        String url = "jdbc:mysql://%s?characterEncoding=utf8".formatted(host);
        DataSource dataSource = DataSourceTool.newHikariDataSource(url, username, password);
        Collection<File> files = FileUtils.listFiles(new File(directory), null, true);
        AtomicInteger successCount = new AtomicInteger();

        try (ForkJoinPool forkJoinPool = ForkJoinPool.commonPool()) {
            for (File file : files) {
                String filePath = file.getAbsolutePath();
                forkJoinPool.execute(() -> {
                    executeBatch(dataSource, filePath);
                    successCount.incrementAndGet();
                });
            }
            forkJoinPool.awaitQuiescence(Long.MAX_VALUE, TimeUnit.SECONDS);
            log.info("SqlFileToMysql.executeToMysql successCount = {}", successCount.get());
        }
    }

    /**
     * 根据指定的文件路径，获取sql语句列表
     * @param filePath 文件路径
     * @return sql语句列表
     */
    private static List<String> getSqlList(String filePath) {
        StringBuilder stringBuilder = new StringBuilder();
        List<String> sqlList = new ArrayList<>();
        try {
            try (Stream<String> lines = Files.lines(Paths.get(filePath), StandardCharsets.UTF_8)) {
                Iterator<String> iterator = lines.iterator();
                while (iterator.hasNext()) {
                    String line = iterator.next();
                    if (line.isEmpty()) {
                        sqlList.add(stringBuilder.toString());
                        stringBuilder = new StringBuilder();
                    }
                    stringBuilder.append(line);
                }
                sqlList.add(stringBuilder.toString());
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return sqlList;
    }

    /**
     * 执行批量sql
     * @param dataSource 数据源
     * @param filePath 文件路径
     */
    private static void executeBatch(DataSource dataSource , String filePath) {
        List<String> sqlList = getSqlList(filePath);

        try (Connection connection = dataSource.getConnection();){
            Statement statement = connection.createStatement();
            for (String sql : sqlList) {
                statement.addBatch(sql);
            }
            statement.executeBatch();
        } catch (Exception e) {
            log.error("SqlFileToMysql.executeBatch error filePath = {}", filePath, e);
        }

        log.info("SqlFileToMysql.executeToMysql finish file = {}", filePath);
    }
}
